package cn.remex.core.util;

import cn.remex.core.exception.NestedException;
import cn.remex.core.exception.ServiceCode;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;

/**
 * 名称：
 * 缩写：
 * 用途：
 * Created by yangy on 2016/6/23 0023.
 */
public class ExcelHelper {

	/**
	 * @param titleMap         title的映射转换
	 * @param _input           输入对象
	 * @param rowConsumer      行消费对象
	 * @param sheetIndexOrName 表单序号
	 */
	public static void forEachExcelSheetRow(Object _input, Object sheetIndexOrName, Map<String, String> titleMap, Consumer<Map<String, Object>> rowConsumer) {
		List<String> titles = new ArrayList<>();
		Map<String, Object> rowData = new HashMap<>();

		boolean isOldExcel = false;

		InputStream in = null;
		try {
			Assert.notNullAndEmpty(_input, ServiceCode.ERROR, "处理Excel的输入参数仅支持File / InputStream 两种且不能为空");
			if (_input instanceof File) {
				isOldExcel = ((File)_input).getName().toLowerCase().endsWith(".xls");
				in = new BufferedInputStream(new FileInputStream((File) _input));
			} else if (_input instanceof InputStream) {
				in = new BufferedInputStream((InputStream) _input);
			}
			Assert.notNullAndEmpty(in, ServiceCode.ERROR, "处理Excel的输入参数仅支持File / InputStream 两种且不能为空");

			Workbook wb = isOldExcel ? new HSSFWorkbook(in) : new XSSFWorkbook(in);

			Sheet st = sheetIndexOrName instanceof String ? wb.getSheet(sheetIndexOrName.toString()) : wb.getSheetAt(Integer.parseInt(sheetIndexOrName.toString()));
			Assert.notNullAndEmpty(st, ServiceCode.ERROR, "处理Excel时没有根据传入的sheetIndexOrName找到对应的sheet，主要参数传整数则根据第几个sheet获取，字符串则根据sheetName获取，sheetIndexOrName=" + sheetIndexOrName);

			Param<Integer> physicalColCount = new Param<>(0);//导入的行的列数要大于1列
			st.forEach(row -> {
				int curPhysicalColCount = 0;
				if ((curPhysicalColCount = row.getPhysicalNumberOfCells()) <= 1) {//导入的行的列数要大于1列
					return;//跳过无用的行
				}
				if (physicalColCount.param == 0) {//找到的第一行是titles，且把总列数确定下来
					physicalColCount.param = curPhysicalColCount;
					row.forEach(titleCell -> titles.add(titleCell.getStringCellValue()));
				}

				if ((physicalColCount.param > 1) && row.getPhysicalNumberOfCells() >= 1) {
					int i = 0;
					for (Cell dataCell : row) {
						Object value = null;
						switch (dataCell.getCellType()) {
							case HSSFCell.CELL_TYPE_STRING:
								value = dataCell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (DateUtil.isCellDateFormatted(dataCell)) {
									Date date = dataCell.getDateCellValue();
									if (date != null) {
										value = new SimpleDateFormat("yyyy-MM-dd hh:mm:dd")
												.format(date);
									}
								} else {
									value = new DecimalFormat("0").format(dataCell
											.getNumericCellValue());
								}
								break;
						}

						rowData.put(Judgment.nullOrBlank(titleMap.get(titles.get(i))) ? titles.get(i) : titleMap.get(titles.get(i)), value);
						i++;
					}
				}
				rowConsumer.accept(rowData);
			});

		} catch (NumberFormatException e) {
			throw new NestedException(ServiceCode.ERROR, "Excel 处理时，sheet名字只能是整数或字符串", e);
		} catch (Exception e) {
			throw new NestedException(ServiceCode.ERROR, "Excel 处理解析错误", e);
		}
	}


	public static void main(String[] args) {
		String downUrl = "http://work.xune.com.cn/dhglplus/rpt/peculiar/zjpay";
		String loginUrl = "http://work.xune.com.cn/dhglplus/logon";
		Map<String, Object> loginParams = MapHelper.toMap("username", "admin", "password", "fjc^&*678", "logicSysNo", "DKSP");

		File needCashExcelFile = WebDavFileUtil.downloadByGetWithTomcatLogin(loginUrl, loginParams, downUrl, "/Users/xinxinran/work/tmp/wokao.xls");

		Map<String, String> titleMap = new HashMap<>();
		titleMap.put("明细流水号", "明细流水号");
		titleMap.put("金额(元)", "金额(元)");
		titleMap.put("银行名称", "银行名称");
		titleMap.put("账户类型", "账户类型");
		titleMap.put("账户名称", "账户名称");
		titleMap.put("账户号码", "账户号码");
		titleMap.put("分支行", "分支行");
		titleMap.put("省份", "省份");
		titleMap.put("城市", "城市");
		titleMap.put("结算标识", "结算标识");
		titleMap.put("备注", "备注");
		titleMap.put("证件类型", "证件类型");
		titleMap.put("证件号码", "证件号码");
		titleMap.put("手机号", "手机号");
		titleMap.put("电子邮箱", "电子邮箱");
		titleMap.put("协议用户编号", "协议用户编号");

		forEachExcelSheetRow(needCashExcelFile, 0, titleMap, rowData -> {
			System.out.println(rowData.get("明细流水号"));
		});
	}
}
